import { Connection } from 'mysql'

class SQLWhere {
    type: string
    condition: string[]

    constructor() {
        this.type = "and" // and | or | not | ...
        this.condition = []
    }
}

class SQLOptions {
    group_by: string
    order_by: string
    limit: string | null

    constructor() {
        this.group_by = ""
        this.order_by = ""
        this.limit = null
    }
}

class SqlDataValue {
    type: string
    value: any

    constructor() {
        this.type = 'string'
        this.value = null
    }
}

class SQLObj {
    query: string // query: insert/delete/update/select
    table: string // table name
    data: Map<string, SqlDataValue> // data
                            // for select, use ("key": anything)
                            // for others, use ("key": value)
    where: SQLWhere // change position
    options: SQLOptions // options

    constructor() {
        this.query = "select"
        this.table = ""
        this.data = new Map([])
        this.where = new SQLWhere()
        this.options = new SQLOptions()
    }
}

function getSQLObj_save():object {
    return {
        "sql": "",
        "value": []
    }
}

function _structureAnalysis(sqlObj: SQLObj) {

    // console.log(sqlObj)

    let dataKey = []
    let dataValue = []
    let optionKey = []
    let whereSQL = ""
    let hasWhere = false

    // 读取键
    // console.log(sqlObj.data.keys())
    for (var key of sqlObj.data.keys()) {
        if (sqlObj.query == "update") {
            if (sqlObj.data.get(key)?.type == 'number')
                dataKey.push([key, sqlObj.data.get(key)?.value].join("="))
            else
                dataKey.push([key, '\'' + sqlObj.data.get(key)?.value + '\''].join("="))
        } else {
            dataKey.push(key)
        }
        let t_k: SqlDataValue | undefined = sqlObj.data.get(key)
        if (typeof t_k == undefined) console.error('t_k is undefined!')
        else {
            if (t_k?.type == 'number') dataValue.push(t_k.value)
            // @ts-ignore
            else dataValue.push('\'' + t_k.value + '\'')
        }
        
    }
    // console.log(dataValue, dataKey)

    // 判断是否有where条件
    hasWhere = sqlObj["where"]["condition"].length != 0
    whereSQL = "where " + sqlObj["where"]["condition"].join(` ${sqlObj["where"]["type"]} `)

    // 读取语句中的order和group
    if (sqlObj["options"].group_by != "")
        optionKey.push(["group by", sqlObj["options"].group_by].join(" "))
    if (sqlObj["options"].order_by != "")
        optionKey.push(["order by", sqlObj["options"].order_by].join(" "))

    // 拼装sql
    let sqlStr: object = {
        "update": `update ${sqlObj["table"]} set ${dataKey.join(",")} ${hasWhere ? whereSQL : ""};`,
        "select": `select ${dataKey.join(",")} from ${sqlObj["table"]} ${hasWhere ? whereSQL : ""};`,
        "delete": `delete from ${sqlObj["table"]} ${hasWhere ? whereSQL : ""};`,
        "insert": `insert into ${sqlObj["table"]} (${dataKey.join(",")}) values(${dataValue.join(",")});`
    }

    let result: object = getSQLObj_save()
    // @ts-ignore
    result["sql"] = sqlStr[sqlObj["query"]]
    // @ts-ignore
    result["value"] = sqlObj["query"] == "select" ? [] : dataValue

    return result
}

// 异步回调函数
function dataBaseControl(conn: Connection, sql: string, args: Array<string>, callback: Function) {
    if (args == null || args.length == 0) {
        conn.query(sql, function (error, results, _fields) {
            if (error) {
                console.error(error)
                callback(null)
                return
            }
            callback(results)
        })
    } else {
        conn.query(sql, args, function(error, results, _fields) {
            if (error) {
                console.error(error)
                callback(null)
                return
            }
            callback(results)
        })
    }
}

// 异步执行sql语句
function ControlAPI_obj(conn: Connection ,data: SQLObj, callback: Function) {
    var sqlObj = _structureAnalysis(data)
    // @ts-ignore
    dataBaseControl(conn, sqlObj["sql"], sqlObj["value"], (results: Array<any>) => {
        if (results == null || results.length == 0)
            callback(null)
        else
            callback(results)
    })
}

// 同步执行单条sql语句
function ControlAPI_obj_async(conn: Connection, data: SQLObj) {
    var sqlObj = _structureAnalysis(data)
    return new Promise((resolved, rejected) => {
        // @ts-ignore
        dataBaseControl(conn, sqlObj["sql"], sqlObj["value"], (results: Array<any>) => {
            if (results == null)
                rejected(null)
            else
                resolved(results)
        })
    })
}

// 同步执行多条sql语句
function ControlAPI_objs_async(conn: Connection, ...vars: Array<SQLObj>) {
    var len = vars.length
    let promiseList = []
    for (let i = 0; i < len; i += 1) {
        let sqlObj = _structureAnalysis(vars[i])
        promiseList.push(new Promise((resolved, rejected) => {
            // @ts-ignore
            dataBaseControl(conn, sqlObj["sql"], sqlObj["value"], (results: Array<any>) => {
                if (results == null)
                    rejected(null)
                else
                    resolved(results)
            })
        }))
    }
    return Promise.all(promiseList)
}

export default {
    SQLObj,
    ControlAPI_obj,
    ControlAPI_obj_async,
    ControlAPI_objs_async
}